<?php
/**
 * 数据库初始化脚本
 * 专门用于创建数据库表和初始数据
 */

echo "开始数据库初始化...\n";

// 数据库配置
$config = [
    'host' => 'localhost',
    'username' => 's6814949',
    'password' => 'xssx123456',
    'database' => 's6814949'
];

try {
    // 创建数据库连接
    $pdo = new PDO(
        "mysql:host={$config['host']};charset=utf8mb4", 
        $config['username'], 
        $config['password']
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "数据库连接成功\n";
    
    // 创建数据库（如果不存在）
    $pdo->exec("CREATE DATABASE IF NOT EXISTS {$config['database']} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "数据库创建/确认完成\n";
    
    // 选择数据库
    $pdo->exec("USE {$config['database']}");
    
    // 删除现有表（重新创建）
    $drop_tables = [
        "SET FOREIGN_KEY_CHECKS = 0",
        "DROP TABLE IF EXISTS daily_profits",
        "DROP TABLE IF EXISTS transactions", 
        "DROP TABLE IF EXISTS user_positions",
        "DROP TABLE IF EXISTS price_history",
        "DROP TABLE IF EXISTS game_rounds",
        "DROP TABLE IF EXISTS users",
        "SET FOREIGN_KEY_CHECKS = 1"
    ];
    
    foreach ($drop_tables as $sql) {
        $pdo->exec($sql);
    }
    echo "清理旧表完成\n";
    
    // 创建表
    $create_tables = [
        // 1. 用户表
        "CREATE TABLE users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) UNIQUE NOT NULL,
            password VARCHAR(255) NOT NULL,
            balance DECIMAL(10,2) DEFAULT 1000.00,
            frozen_profit DECIMAL(10,2) DEFAULT 0.00,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        // 2. 游戏轮次表
        "CREATE TABLE game_rounds (
            id INT AUTO_INCREMENT PRIMARY KEY,
            round_number INT NOT NULL,
            status ENUM('trading', 'settlement', 'break') DEFAULT 'trading',
            start_time TIMESTAMP NOT NULL,
            end_time TIMESTAMP NOT NULL,
            settlement_price DECIMAL(8,2) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        // 3. 价格历史表
        "CREATE TABLE price_history (
            id INT AUTO_INCREMENT PRIMARY KEY,
            round_id INT,
            price DECIMAL(8,2) NOT NULL,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_round_id (round_id),
            INDEX idx_timestamp (timestamp)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        // 4. 用户持仓表
        "CREATE TABLE user_positions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            round_id INT NOT NULL,
            round_number INT NOT NULL,
            quantity DECIMAL(10,4) NOT NULL,
            purchase_price DECIMAL(8,2) NOT NULL,
            purchase_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            is_active BOOLEAN DEFAULT TRUE,
            INDEX idx_user_id (user_id),
            INDEX idx_round_id (round_id),
            INDEX idx_is_active (is_active)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        // 5. 交易记录表
        "CREATE TABLE transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            round_id INT NOT NULL,
            type ENUM('buy', 'sell') NOT NULL,
            quantity DECIMAL(10,4) NOT NULL,
            price DECIMAL(8,2) NOT NULL,
            amount DECIMAL(10,2) NOT NULL,
            profit DECIMAL(10,2) DEFAULT 0.00,
            transaction_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_id (user_id),
            INDEX idx_round_id (round_id),
            INDEX idx_transaction_time (transaction_time)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci",

        // 6. 每日盈利记录表
        "CREATE TABLE daily_profits (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            profit_amount DECIMAL(10,2) NOT NULL,
            earned_date DATE NOT NULL,
            is_available BOOLEAN DEFAULT FALSE,
            available_date DATE NOT NULL,
            INDEX idx_user_id (user_id),
            INDEX idx_available_date (available_date),
            INDEX idx_is_available (is_available)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
    ];

    foreach ($create_tables as $sql) {
        $pdo->exec($sql);
    }
    echo "数据表创建完成\n";
    
    // 创建测试用户
    $test_users = [
        ['admin', 'admin123'],
        ['player1', '123456'],
        ['player2', '123456']
    ];
    
    foreach ($test_users as $user) {
        $hashed_password = password_hash($user[1], PASSWORD_DEFAULT);
        $stmt = $pdo->prepare("INSERT IGNORE INTO users (username, password) VALUES (?, ?)");
        $stmt->execute([$user[0], $hashed_password]);
    }
    echo "测试用户创建完成\n";
    
    // 创建初始游戏轮次
    $start_time = date('Y-m-d H:i:s');
    $end_time = date('Y-m-d H:i:s', strtotime('+15 minutes'));
    
    $stmt = $pdo->prepare("INSERT INTO game_rounds (round_number, status, start_time, end_time) VALUES (1, 'trading', ?, ?)");
    $stmt->execute([$start_time, $end_time]);
    $round_id = $pdo->lastInsertId();
    
    // 添加初始价格
    $stmt = $pdo->prepare("INSERT INTO price_history (round_id, price) VALUES (?, 100.00)");
    $stmt->execute([$round_id]);
    
    echo "初始游戏轮次创建完成\n";
    
    echo "\n=== 安装完成 ===\n";
    echo "数据库: {$config['database']}\n";
    echo "测试用户:\n";
    echo "- admin / admin123 (管理员)\n";
    echo "- player1 / 123456\n";
    echo "- player2 / 123456\n";
    echo "\n现在可以访问网站了！\n";
    
} catch (PDOException $e) {
    echo "错误: " . $e->getMessage() . "\n";
    exit(1);
}
?>
